// ------------------------------------------------------------------------------------------
// Licensed by Interprise Solutions.
// http://www.InterpriseSolutions.com
// For details on this license please visit  the product homepage at the URL above.
// THE ABOVE NOTICE MUST REMAIN INTACT.
// ------------------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Security;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Web;
using System.Web.Caching;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Xsl;
using InterpriseSuiteEcommerceCommon.Extensions;
using InterpriseSuiteEcommerceCommon.Tool;

namespace InterpriseSuiteEcommerceCommon
{
    public struct XmlPackageParam
    {
        public XmlPackageParam(string name, string value)
        {
            this.Name = name;
            this.Value = value;
        }

        public string Name;
        public string Value;

        public static List<XmlPackageParam> FromString(string ampersandDelimetedParams)
        {
            var xmlParams = new List<XmlPackageParam>();

            foreach (string s in ampersandDelimetedParams.Split('&'))
            {
                string[] s2 = s.Split(new Char[] { '=' }, 2);
                string ParamName = string.Empty;
                try
                {
                    ParamName = s2[0];
                }
                catch { }
                string ParamValue = string.Empty;
                try
                {
                    ParamValue = HttpUtility.UrlDecode(s2[1]);
                }
                catch { }

                if (ParamName.Length != 0)
                {
                    xmlParams.Add(new XmlPackageParam(ParamName, ParamValue));
                }
            }

            return xmlParams;
        }
    }

    /// <summary>
    /// Summary description for XmlPackage2.
    /// </summary>
    public class XmlPackage2: IDisposable
    {
        #region Private Variables
        private static readonly string readonly_DefaultUserQueryName = "UserQuery";
        private string m_PackageName = string.Empty;
        private string m_PackageUrl = string.Empty;
        private Customer m_ThisCustomer = null;
        private int m_SkinID = 1;
        private XmlDocument m_PackageDocument = null;
        private XmlDocument m_DataDocument = new XmlDocument();
        private string m_DocumentSource = string.Empty;
        private string m_SQLData = string.Empty;
        private string m_WebData = string.Empty;
        private string m_EntityHelpersXml = string.Empty;
        private List<XmlPackageParam> m_AdditionalRuntimeParms = null;
        private string m_UserSpecifiedQuery = string.Empty;
        private XmlDocument m_TransformDocument = new XmlDocument();
        private XslCompiledTransform m_Transform;
        private XsltArgumentList m_TransformArgumentList = new XsltArgumentList();
        private string m_TransformSource = string.Empty; //full local path to the transform document
        private DataSet m_Data = null;
        private string m_FinalResult = string.Empty;
        private string m_SectionTitle = string.Empty;
        private int m_NumRows = 0; // this is set AFTER transformString is called, and ONLY set for the first query in the package!
        private readonly string[] ro_ServerVariablesList = { "HTTP_HOST", "HTTP_USER_AGENT", "AUTH_TYPE", "AUTH_USER", "AUTH_PASSWORD", "HTTPS", "LOCAL_ADDR", "PATH_INFO", "PATH_TRANSLATED", "SCRIPT_NAME", "SERVER_NAME", "SERVER_PORT_SECURE" };
        private Hashtable m_RuntimeQueries = new Hashtable();
        private Hashtable m_RuntimeParameters = new Hashtable();
        private bool m_RequiresParser = false;
        private string m_LocaleSetting = string.Empty;
        private string m_CurrencySetting = string.Empty;

        private decimal m_Version = 0;
        private bool m_IsDebug = false;
        private string m_displayname = string.Empty;
        private bool m_IncludesEntityHelper = false;
        private bool HasISENameSpace = false;
        private bool m_AllowEngine = false;

        private string m_ContentType = "text/html";
        private string m_SETitle = string.Empty;
        private string m_SEKeywords = string.Empty;
        private string m_SEDescription = string.Empty;
        private string m_SENoScript = string.Empty;
        private string m_SqlDebug = string.Empty;

        private bool m_PackageFound = false;
        private DataTable m_SystemT = new DataTable("System");
        private XmlDocument m_SystemData = new XmlDocument();
        private bool disposed = false;

        private const string NULL_STRING = "null";
        #endregion

        #region Constructors

        public XmlPackage2(string PackageName, System.Xml.Linq.XElement LinqToXMLParameter)
            : this(PackageName, null, 1, string.Empty, null, string.Empty, true, LinqToXMLParameter) { }

        public XmlPackage2(string PackageName, Customer cust)
            : this(PackageName, cust, cust.SkinID, string.Empty, null) { }

        public XmlPackage2(string PackageName)
            : this(PackageName, null, 1, string.Empty, null)
        { }

        public XmlPackage2(string PackageName, List<XmlPackageParam> AdditionalQueryStringParms)
            : this(PackageName, null, 1, string.Empty, AdditionalQueryStringParms)
        { }

        public XmlPackage2(string PackageName, int SkinID)
            : this(PackageName, null, SkinID, string.Empty, null) { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID)
            : this(PackageName, cust, SkinID, string.Empty, null) { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID, string UserQuery)
            : this(PackageName, cust, SkinID, UserQuery, null)
        { }

        public XmlPackage2(string PackageName, Customer cust, string UserQuery)
            : this(PackageName, cust, cust.SkinID, UserQuery, null)
        { }

        // remember, AdditionalQueryStringParms values must be properly urlencoded
        public XmlPackage2(string PackageName, Customer cust, string UserQuery, List<XmlPackageParam> AdditionalQueryStringParms)
            : this(PackageName, cust, cust.SkinID, UserQuery, AdditionalQueryStringParms)
        { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID, string UserQuery, List<XmlPackageParam> AdditionalRuntimeParms)
            : this(PackageName, cust, SkinID, UserQuery, AdditionalRuntimeParms, string.Empty)
        { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID, string UserQuery, List<XmlPackageParam> AdditionalRuntimeParms, string OnlyRunNamedQuery)
            : this(PackageName, cust, SkinID, UserQuery, AdditionalRuntimeParms, OnlyRunNamedQuery, true) { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID, string UserQuery, List<XmlPackageParam> AdditionalRuntimeParms, string OnlyRunNamedQuery, bool UseExtensions)
            :this(PackageName, cust, SkinID, UserQuery, AdditionalRuntimeParms, OnlyRunNamedQuery, UseExtensions, null) { }

        public XmlPackage2(string PackageName, Customer cust, int SkinID, string UserQuery, List<XmlPackageParam> AdditionalRuntimeParms, string OnlyRunNamedQuery, bool UseExtensions, XElement LinqToXMLParameter)
        {
            m_PackageName = PackageName;
            m_ThisCustomer = cust;
            XmlParameter = LinqToXMLParameter;
            if (m_ThisCustomer == null)
            {
                try
                {
                    var principal = HttpContext.Current.User as InterpriseSuiteEcommercePrincipal;
                    if (principal != null)
                    {
                        m_ThisCustomer = principal.ThisCustomer;
                    }
                }
                catch { }
            }
            if (m_ThisCustomer == null)
            {
                m_ThisCustomer = Customer.MakeAnonymous();
                m_LocaleSetting = m_ThisCustomer.LocaleSetting;
                m_CurrencySetting = m_ThisCustomer.CurrencyCode;
            }
            else
            {
                m_LocaleSetting = m_ThisCustomer.LocaleSetting;
                m_CurrencySetting = m_ThisCustomer.CurrencyCode;
            }

            if (LinqToXMLParameter != null)
            {
                m_SkinID = ThisCustomer.SkinID;
            }
            else
            {
                m_SkinID = SkinID;
            }
            
            m_AdditionalRuntimeParms = AdditionalRuntimeParms;
            m_UserSpecifiedQuery = UserQuery.Trim();

            if (m_UserSpecifiedQuery.Length != 0)
            {
                AddRunTimeQuery(readonly_DefaultUserQueryName, m_UserSpecifiedQuery);
            }

            if (null != m_AdditionalRuntimeParms && m_AdditionalRuntimeParms.Count > 0)
            {
                foreach (var param in m_AdditionalRuntimeParms)
                {
                    AddRunTimeParam(param.Name, param.Value);
                }
            }

            //Load the package (from cache first) and create a Transform using the packageTransform
            //then get the data based on the queries in the package and any user queries
            //Add checking for mobile andhttp://192.168.1.150:100/mobile/p-293-4gb-microsd-memory-card.aspx ordinary ise page. 
            //Must have different cache name

            string mobilebrowser = string.Empty;
            if (CurrentContext.IsRequestingFromMobileMode(ThisCustomer))
            {
                mobilebrowser = "mobile";
            }

            string CacheName = mobilebrowser + PackageName + "_" + m_SkinID.ToString() + "_" + m_LocaleSetting;
            m_PackageDocument = CachingFactory.ApplicationCachingEngineInstance.GetItem<XmlDocument>(CacheName);

            if (m_PackageDocument == null)
            {
                m_PackageUrl = FullPackageUrl(PackageName, m_SkinID);
                m_TransformSource = CommonLogic.SafeMapPath(m_PackageUrl);
                if (!CommonLogic.FileExists(m_TransformSource))
                {
                    m_PackageFound = false;
                }

                m_PackageDocument = new XmlDocument();

                try
                {
                    m_PackageDocument.Load(m_TransformSource);
                    CachingFactory.ApplicationCachingEngineInstance.AddItem(CacheName, m_PackageDocument, new CacheDependency(m_TransformSource));
                }
                catch (Exception ex)
                {
                    string XMsg = CommonLogic.GetExceptionDetail(ex, "<br/>");
                    if (XMsg.IndexOf("could not find file", StringComparison.InvariantCultureIgnoreCase) != -1)
                    {
                        // clean up message to make it more "helpful"
                        XMsg = "The XmlPackage file could not be found. The search tree is: skins/skin_{activeskinid}/XmlPackages and then the /XmlPackages folders in your web site. Please make sure that you have FTP'd the file up to the server!";
                    }
                    throw new ArgumentException("Error in XmlPackage(.Load), Package=[" + PackageName + "], Msg=[" + XMsg + "]");
                }
            }

            var ExtObj = new XSLTExtensions(m_ThisCustomer, m_SkinID);

            var helperPackageNode = m_PackageDocument.SelectSingleNode("/package/" + DomainConstants.XML_HELPER_PACKAGE_NAME);
            if (helperPackageNode != null && helperPackageNode.Attributes["name"] != null)
            {
                string helperPackageName = helperPackageNode.Attributes["name"].Value;
                ExtObj.XmlPackageHelperTemplateList = helperPackageName.Replace(" ", string.Empty).Split(',');
            }

            if (UseExtensions)
            {
                m_TransformArgumentList.AddExtensionObject("urn:ise", ExtObj);

                var objExtConfig = XsltObjects.ExtensionConfiguration.GetExtensionConfiguration("xsltobjects");
                if (objExtConfig != null)
                {
                    Object extObj;
                    foreach (XsltObjects.Extension ext in objExtConfig.Extensions.Values)
                    {
                        extObj = ExtensionObjects.CreateExtension(ext.Type, m_ThisCustomer, m_SkinID, null);
                        m_TransformArgumentList.AddExtensionObject(ext.Attributes["namespace"], extObj);
                    }
                }
            }

            var packageNode = m_PackageDocument.SelectSingleNode("/package");

            //Assign package property vairables
            var reqparser = packageNode.Attributes["RequiresParser"];
            if (reqparser != null && reqparser.InnerText == "true")
            {
                m_RequiresParser = true;
            }


            if (packageNode.Attributes["version"] != null)
            {
                try
                {
                    m_Version = Localization.ParseUSDecimal(packageNode.Attributes["version"].InnerText);
                }
                catch
                {
                    throw new Exception("Invalid package version specified");
                }
            }
            else
            {
                m_Version = 2.0M;
            }

            XmlNode debug = packageNode.Attributes["debug"];
            if ((debug != null && debug.InnerText == "true") || AppLogic.AppConfigBool("XmlPackage.DumpTransform"))
            {
                m_Transform = new XslCompiledTransform(true);
                m_IsDebug = true;
            }

            XmlNode includesentityhelper = packageNode.Attributes["includeentityhelper"];
            if (includesentityhelper != null && includesentityhelper.InnerText == "true")
            {
                m_IncludesEntityHelper = true;
            }

            XmlNode allowengine = packageNode.Attributes["allowengine"];
            if (allowengine != null && allowengine.InnerText == "true")
            {
                m_AllowEngine = true;
            }

            XmlNode displayname = packageNode.Attributes["displayname"];
            if (displayname != null)
            {
                m_displayname = displayname.InnerText;
            }

            XmlNode contenttype = packageNode.Attributes["contenttype"];
            if (contenttype != null)
            {
                m_ContentType = contenttype.InnerText;
            }

            XmlNodeList RuntimeNodes = m_PackageDocument.SelectNodes("/package/runtime");
            foreach (XmlNode n in RuntimeNodes)
            {
                switch (n.Attributes["paramtype"].InnerText)
                {
                    case "appconfig":
                        AddRunTimeParam(n.Attributes["paramname"].InnerText, AppLogic.AppConfig(n.Attributes["requestparamname"].InnerText));
                        break;
                    case "request":
                        AddRunTimeParam(n.Attributes["paramname"].InnerText, CommonLogic.ParamsCanBeDangerousContent(n.Attributes["requestparamname"].InnerText));
                        break;
                }
            }

            HasISENameSpace = (m_PackageDocument.SelectSingleNode("/package/PackageTransform").FirstChild.Attributes["xmlns:ise"] != null);

            //Load transform
            if (m_PackageDocument.SelectSingleNode("/package/PackageTransform").FirstChild == null)
            {
                throw new Exception("The PackageTransform element must contain an xsl:stylesheet node");
            }

            XmlUrlResolver resolver = new XmlUrlResolver();
            resolver.Credentials = System.Net.CredentialCache.DefaultCredentials;
            if (this.IsDebug || AppLogic.AppConfigBool("XmlPackage.DumpTransform"))
            {
                string xFormFile = string.Empty;
                xFormFile = CommonLogic.SafeMapPath(string.Format("images/{0}_{1}.runtime.xsl", m_PackageName, "store"));
                StreamWriter sw = File.CreateText(xFormFile);
                sw.WriteLine(XmlCommon.PrettyPrintXml(m_PackageDocument.SelectSingleNode("/package/PackageTransform").InnerXml));
                sw.Close();

                try
                {
                    m_Transform.Load(xFormFile, XsltSettings.TrustedXslt, resolver);
                }
                catch (SecurityException)
                {
                    m_Transform = new XslCompiledTransform(false); //if it failed it must be in Medium trust so turn off debugging in the tranform itself.
                    m_Transform.Load(xFormFile, XsltSettings.TrustedXslt, resolver);
                }
            }
            else 
            {
                m_Transform = CachingFactory.ApplicationCachingEngineInstance.GetItem<XslCompiledTransform>(m_PackageDocument.BaseURI);
                if (m_Transform == null)
                {
                    m_Transform = new XslCompiledTransform(false);
                    m_Transform.Load(m_PackageDocument.SelectSingleNode("/package/PackageTransform").FirstChild, XsltSettings.TrustedXslt, resolver);
                    //Make this work with NAS environment
                    if (m_PackageDocument.BaseURI.Split(':').Length > 2)
                    {
                        CachingFactory.ApplicationCachingEngineInstance.AddItem(m_PackageDocument.BaseURI, m_Transform, new CacheDependency(m_PackageDocument.BaseURI.Replace("file:///", "")));
                    }
                    else
                    {
                        CachingFactory.ApplicationCachingEngineInstance.AddItem(m_PackageDocument.BaseURI, m_Transform, new CacheDependency(m_PackageDocument.BaseURI.Replace("file:", "").Replace("/", "\\")));
                    }
                }
            }

            //Create Xml DataDocument
            CultureInfo tmpCurrentCulture = Thread.CurrentThread.CurrentCulture;
            CultureInfo tmpCurrentUICulture = Thread.CurrentThread.CurrentUICulture;
            Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(Localization.GetSqlServerLocale());
            Thread.CurrentThread.CurrentUICulture = new CultureInfo(Localization.GetSqlServerLocale());
            using (StringReader sr = new StringReader("<root/>"))
            {
                using (XmlReader xr = XmlReader.Create(sr))
                {
                    m_DataDocument.Load(xr);
                }
            }
            InitializeSystemData();
            GetSqlData(OnlyRunNamedQuery);
            Thread.CurrentThread.CurrentCulture = tmpCurrentCulture;
            Thread.CurrentThread.CurrentUICulture = tmpCurrentUICulture;
            GetWebData();

            //Add EntityHelper data to the Xml DataDocument
            if (this.IncludesEntityHelper)
            {
                m_DataDocument.DocumentElement.AppendChild(m_DataDocument.CreateNode(XmlNodeType.Element, "EntityHelpers", ""));
                foreach (string s in AppLogic.ro_SupportedEntities)
                {
                    m_DataDocument.SelectSingleNode("/root/EntityHelpers").AppendChild(m_DataDocument.CreateNode(XmlNodeType.Element, s, ""));
                    foreach (XmlNode x in AppLogic.LookupHelper(s).m_TblMgr.XmlDoc.SelectNodes("/root/Entity"))
                    {
                        m_DataDocument.SelectSingleNode("/root/EntityHelpers/" + s).AppendChild(m_DataDocument.ImportNode(x, true));
                    }
                }
            }
           
            //Add Search Engine Settings
            ProcessSESettings();
            ProcessAfterActions();
        }

        #endregion

        #region Public Properties
        public DataSet Data
        {
            get
            {
                if (m_Data == null)
                {
                    m_Data = new DataSet("root");
                }
                return m_Data;
            }
        }
        public Customer ThisCustomer
        {
            get
            {
                return m_ThisCustomer;
            }
        }
        public int SkinID
        {
            get
            {
                return m_SkinID;
            }
        }
        public bool PackageFound
        {
            get
            {
                return m_PackageFound;
            }
        }
        public string ContentType
        {
            get
            {
                return m_ContentType;
            }
        }
        public string SETitle
        {
            get
            {
                return m_SETitle;
            }
        }
        public string FinalResult
        {
            get
            {
                return m_FinalResult;
            }
        }
        public string SectionTitle
        {
            get
            {
                return m_SectionTitle;
            }
        }
        public string SEKeywords
        {
            get
            {
                return m_SEKeywords;
            }
        }
        public string SEDescription
        {
            get
            {
                return m_SEDescription;
            }
        }
        public string SENoScript
        {
            get
            {
                return m_SENoScript;
            }
        }
        public string Name
        {
            get
            {
                return m_PackageName;
            }
        }
        public bool RequiresParser
        {
            get
            {
                return m_RequiresParser;
            }
        }
        public string URL
        {
            get
            {
                return m_PackageUrl;
            }
        }
        public XsltArgumentList TransformArgumentList
        {
            get
            {
                if (m_TransformArgumentList == null)
                {
                    m_TransformArgumentList = new XsltArgumentList();
                }
                return m_TransformArgumentList;
            }
            set
            {
                m_TransformArgumentList = value;
            }
        }
        public string XmlSystemData
        {
            get
            {
                return m_SystemData.OuterXml;
            }
        }
        public string XmlSqlData
        {
            get
            {
                return m_SQLData;
            }
        }
        public XmlDocument XmlDataDocument
        {
            get
            {
                return m_DataDocument;
            }
        }
        public XmlDocument TransformDocument
        {
            get
            {
                return m_TransformDocument;
            }
        }
        public XmlDocument PackageDocument
        {
            get
            {
                return m_PackageDocument;
            }
        }
        public XslCompiledTransform Transform
        {
            get
            {
                return m_Transform;
            }
        }
        public decimal Version
        {
            get { return m_Version; }
        }
        public bool IsDebug
        {
            get { return m_IsDebug; }
        }
        public string DisplayName
        {
            get { return m_displayname; }
            set { m_displayname = value; }
        }
        public string SqlDebug
        {
            get { return m_SqlDebug; }
        }
        public bool IncludesEntityHelper
        {
            get { return m_IncludesEntityHelper; }
        }
        public bool AllowEngine
        {
            get { return m_AllowEngine; }
        }
        public XmlNodeList HttpHeaders
        {
            get { return m_PackageDocument.SelectNodes("/package/HTTPHeaders/HTTPHeader"); }
        }

        public XElement XmlParameter { get; set; }
        #endregion
        
        #region Public Methods
        public void Dispose()
        {
            if (!disposed)
            {
                m_SystemT.Dispose();
                disposed = true;
            }
        }

        public string TransformString()
        {

            if (AppLogic.AppConfigBool("XmlPackage.DumpTransform") || this.IsDebug)
            {
                try // don't let logging crash the site
                {
                    string fn = CommonLogic.SafeMapPath(string.Format("images/{0}_{1}.runtime.xml", m_PackageName, "store"));
                    using (StreamWriter sw = File.CreateText(fn))
                    {
                        sw.WriteLine(XmlCommon.PrettyPrintXml(m_DataDocument.InnerXml));
                        sw.Close();
                    }
                    fn = CommonLogic.SafeMapPath(string.Format("images/{0}_{1}.runtime.sql", m_PackageName, "store"));
                    using (StreamWriter sw = File.CreateText(fn))
                    {
                        sw.WriteLine(this.SqlDebug);
                        sw.Close();
                    }
                }
                catch
                { }
            }

            using (MemoryStream ms = new MemoryStream())
            {
                try
                {
                    if (HasISENameSpace && XmlParameter == null)
                    {
                        m_Transform.Transform(m_DataDocument, m_TransformArgumentList, ms);
                        m_RequiresParser = true;
                    }
                    else if (XmlParameter != null)
                    {
                        m_Transform.Transform(XmlParameter.CreateReader(), m_TransformArgumentList, ms);
                    }
                    else
                    {
                        m_Transform.Transform(m_DataDocument, null, ms);
                    }
                }
                catch (Exception ex)
                {
                    string LastTraceName = string.Empty;
                    if (HttpContext.Current.Items.Contains("XmlPackageTracePoint"))
                    {
                        LastTraceName = HttpContext.Current.Items["XmlPackageTracePoint"].ToString();
                    }
                    Exception iex = ex.InnerException;
                    string errors = string.Empty;
                    while (iex != null)
                    {
                        errors = iex.Message + "<br/><br/>";
                        iex = iex.InnerException;
                    }
                    throw new ArgumentException("Last Trace Point=[" + LastTraceName + "]. " + ex.Message + "<br/><br/>" + errors);
                }

                ms.Position = 0;
                using (StreamReader sr = new StreamReader(ms, m_Transform.OutputSettings.Encoding))
                {
                    m_FinalResult = sr.ReadToEnd();
                    //New Menu implementation
                    if (XmlParameter != null) 
                    { 
                        m_FinalResult = TranslateStaticValuesByResource(m_FinalResult); 
                    }
                }
                ms.Close();
            }
            if (AppLogic.AppConfigBool("XmlPackage.DumpTransform") || this.IsDebug)
            {
                // don't let logging crash the site!
                try
                {
                    string fn = CommonLogic.SafeMapPath(string.Format("images/{0}_{1}.xfrm.xml", m_PackageName, "store"));
                    using (StreamWriter sw = File.CreateText(fn))
                    {
                        sw.WriteLine(XmlCommon.PrettyPrintXml(m_FinalResult));
                        sw.Close();
                    }
                }
                catch { }
            }

            return m_FinalResult;
        }

        public string TranslateStaticValuesByResource(string html)
        {
            var pattern = new Regex(@"\(!(.*?)!\)");
            var findAndReplaceStringResourced = new MatchEvaluator(StringResourceMatch);
            return pattern.Replace(html, findAndReplaceStringResourced);
        }

        #endregion 

        #region Private Methods
        private string FullPackageUrl(string PackageName, int SkinID)
        {
            if (!PackageName.EndsWith(".xml.config", StringComparison.InvariantCultureIgnoreCase))
            {
                PackageName += ".xml.config";
            }

            string url = string.Empty;

            // if we have a fully specified path on input, just use it after trying to find locale specific versions:
            if (PackageName.StartsWith("//") || PackageName.IndexOf(":") != -1)
            {
                if (m_ThisCustomer != null)
                {
                    url = PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config");
                }
                if (url.Length == 0 || !CommonLogic.FileExists(url))
                {
                    url = PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config");
                }
                if (url.Length == 0 || !CommonLogic.FileExists(url))
                {
                    url = PackageName;
                }
                return url;
            }

            string appdir = HttpContext.Current.Request.PhysicalApplicationPath;
            string rootUrl = string.Empty, XmlPackageDir = string.Empty, EntityMgrDir = string.Empty;
            string ExtendedAppdir = string.Empty, ExtendedRootUrl = string.Empty, ExtendedXmlPackageDir = string.Empty;

            rootUrl = Path.Combine(appdir, string.Format("skins\\Skin_{0}\\XmlPackages", SkinID.ToString()));
            XmlPackageDir = Path.Combine(appdir, "XmlPackages");
            EntityMgrDir = Path.Combine(appdir, "EntityHelper");

            //Check if customer switch to full mode even if he is in mobile device
            if (CurrentContext.IsRequestingFromMobileMode(ThisCustomer))
            {
                string folderName = CurrentContext.ToValue(DomainConstants.MobileFolderName);

                //Implement this for page concurrency of ISE and Mobile pages.
                //Due to they are running under the same web site and Application Pool
                if (folderName.IsNullOrEmptyTrimmed())
                {
                    folderName = CommonLogic.Application(DomainConstants.MobileFolderName);
                    CurrentContext.Add<string>(DomainConstants.MobileFolderName, folderName);
                }

                ExtendedAppdir = Path.Combine(appdir, "{0}\\".FormatWith(folderName));
                appdir = ExtendedAppdir;

                ExtendedRootUrl = Path.Combine(ExtendedAppdir, string.Format("skins\\Skin_{0}\\XmlPackages", SkinID.ToString()));
                rootUrl = ExtendedRootUrl;

                ExtendedXmlPackageDir = Path.Combine(ExtendedAppdir, "XmlPackages");
                XmlPackageDir = ExtendedXmlPackageDir;

                EntityMgrDir = Path.Combine(ExtendedAppdir, "EntityHelper");
            }

            if (m_ThisCustomer != null)
            {
                url = Path.Combine(rootUrl, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            else
            {
                url = Path.Combine(rootUrl, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url))
            {
                url = Path.Combine(rootUrl, PackageName);
            }
            if ((url.Length == 0 || !CommonLogic.FileExists(url)) && m_ThisCustomer != null)
            {
                url = Path.Combine(XmlPackageDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url) && m_ThisCustomer == null)
            {
                url = Path.Combine(XmlPackageDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url))
            {
                url = Path.Combine(XmlPackageDir, PackageName);
            }
            if ((url.Length == 0 || !CommonLogic.FileExists(url)) && m_ThisCustomer != null)
            {
                url = Path.Combine(EntityMgrDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url) && m_ThisCustomer == null)
            {
                url = Path.Combine(EntityMgrDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url))
            {
                url = Path.Combine(EntityMgrDir, PackageName);
            }
            if ((url.Length == 0 || !CommonLogic.FileExists(url)) && m_ThisCustomer != null)
            {
                url = Path.Combine(ExtendedRootUrl, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url) && m_ThisCustomer == null)
            {
                url = Path.Combine(ExtendedRootUrl, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url))
            {
                url = Path.Combine(ExtendedRootUrl, PackageName);
            }

            if ((url.Length == 0 || !CommonLogic.FileExists(url)) && m_ThisCustomer != null)
            {
                url = Path.Combine(ExtendedXmlPackageDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url) && m_ThisCustomer == null)
            {
                url = Path.Combine(ExtendedXmlPackageDir, PackageName.Replace("xml.config", m_ThisCustomer.LocaleSetting + ".xml.config"));
            }
            if (url.Length == 0 || !CommonLogic.FileExists(url))
            {
                url = Path.Combine(ExtendedXmlPackageDir, PackageName);
            }

            return url;
        }

        private void InitializeSystemData()
        {
            string PrimaryCurrency = Localization.GetPrimaryCurrency();
            string PrimaryCurrencyDisplayLocaleFormat = Currency.GetDisplayLocaleFormat(PrimaryCurrency);

            string xml =
            @"<System>                        
                <IsAdminSite />
                <IsAdminSiteInt />
                <CustomerID />
                <DefaultVATSetting />
                <CustomerVATSetting />
                <UseVATSetting />
                <CustomerLevelID />
                <CustomerLevelName />
                <CustomerFirstName />
                <CustomerLastName />
                <CustomerFullName />
                <SubscriptionExpiresOn />
                <CustomerRoles />
                <IsAdminUser />
                <IsSuperUser />
                <VAT.Enabled />
                <VAT.AllowCustomerToChooseSetting />
                <LocaleSetting />
                <CurrencySetting />
                <CurrencyDisplayLocaleFormat />
                <WebConfigLocaleSetting />
                <SqlServerLocaleSetting />
                <PrimaryCurrency />
                <PrimaryCurrencyDisplayLocaleFormat />
                <Date />
                <Time />
                <SkinID />
                <AffiliateID />
                <IPAddress />
                <QueryStringRAW />
                <UseStaticLinks />
                <PageName />
                <FullPageName />
                <XmlPackageName />
                <StoreUrl />
                <CurrentDateTime />
                <CustomerIsRegistered />
                <RequestedPage />
                <RequestedQuerystring />
                <LanguageCode />
                <WebSiteCode/>
                <CustomerGuid/>
                <ProductFilterID/>
                <ContactGuid/>
                <CustomerCode/>
                <ItemCompareFilter/>
                <ContactCode/>
                <CBNMode/>
            </System>";

            using (StringReader sr = new StringReader(xml))
            {
                using (XmlReader xr = XmlReader.Create(sr))
                {
                    m_SystemData.Load(xr);
                }
            }
            
            if (m_ThisCustomer != null)
            {
                m_SystemData.SelectSingleNode("//CustomerID").InnerText = ThisCustomer.CustomerID.ToString();
                AddRunTimeParam("CustomerID", ThisCustomer.CustomerID.ToString());

                m_SystemData.SelectSingleNode("//DefaultVATSetting").InnerText = AppLogic.AppConfig("VAT.DefaultSetting");
                AddRunTimeParam("DefaultVATSetting", AppLogic.AppConfigUSInt("VAT.DefaultSetting").ToString());

                m_SystemData.SelectSingleNode("//CustomerVATSetting").InnerText = ((int)ThisCustomer.VATSettingRaw).ToString();
                AddRunTimeParam("CustomerVATSetting", ((int)ThisCustomer.VATSettingRaw).ToString());

                m_SystemData.SelectSingleNode("//UseVATSetting").InnerText = ((int)ThisCustomer.VATSettingReconciled).ToString();
                AddRunTimeParam("UseVATSetting", ((int)ThisCustomer.VATSettingReconciled).ToString());

                m_SystemData.SelectSingleNode("//CustomerFirstName").InnerText = XmlCommon.XmlEncode(m_ThisCustomer.FirstName);
                AddRunTimeParam("CustomerFirstName", m_ThisCustomer.FirstName);

                m_SystemData.SelectSingleNode("//CustomerLastName").InnerText = XmlCommon.XmlEncode(m_ThisCustomer.LastName);
                AddRunTimeParam("CustomerLastName", m_ThisCustomer.LastName);

                m_SystemData.SelectSingleNode("//CustomerFullName").InnerText = XmlCommon.XmlEncode((m_ThisCustomer.FirstName + " " + m_ThisCustomer.LastName).Trim());
                AddRunTimeParam("CustomerFullName", (m_ThisCustomer.FirstName + " " + m_ThisCustomer.LastName).Trim());

                string SubExp = string.Empty;
                if (m_ThisCustomer.SubscriptionExpiresOn != System.DateTime.MinValue)
                {
                    SubExp = Localization.ToNativeShortDateString(m_ThisCustomer.SubscriptionExpiresOn);
                }
                m_SystemData.SelectSingleNode("//SubscriptionExpiresOn").InnerText = XmlCommon.XmlEncode(SubExp);
                AddRunTimeParam("SubscriptionExpiresOn", SubExp);

                string CustRoles = AppLogic.GetRoles();
                m_SystemData.SelectSingleNode("//CustomerRoles").InnerText = XmlCommon.XmlEncode(CustRoles);
                AddRunTimeParam("CustomerRoles", CustRoles);

                m_SystemData.SelectSingleNode("//LocaleSetting").InnerText = m_ThisCustomer.LocaleSetting;
                AddRunTimeParam("LocaleSetting", m_ThisCustomer.LocaleSetting);

                m_SystemData.SelectSingleNode("//LanguageCode").InnerText = m_ThisCustomer.LocaleSetting;                
                AddRunTimeParam("LanguageCode", m_ThisCustomer.LanguageCode);

                m_SystemData.SelectSingleNode("//CurrencySetting").InnerText = m_ThisCustomer.LocaleSetting;                
                AddRunTimeParam("CurrencySetting", m_ThisCustomer.CurrencyCode);

                string MM = Currency.GetDisplayLocaleFormat(m_ThisCustomer.CurrencyCode);
                m_SystemData.SelectSingleNode("//CurrencyDisplayLocaleFormat").InnerText = MM;
                AddRunTimeParam("CurrencyDisplayLocaleFormat", MM);

                m_SystemData.SelectSingleNode("//IPAddress").InnerText = m_ThisCustomer.LastIPAddress;
                AddRunTimeParam("IPAddress", m_ThisCustomer.LastIPAddress);

                m_SystemData.SelectSingleNode("//AffiliateID").InnerText = m_ThisCustomer.AffiliateID.ToString();
                AddRunTimeParam("AffiliateID", m_ThisCustomer.AffiliateID.ToString());

                //dwyt
                m_SystemData.SelectSingleNode("//ProductFilterID").InnerText = m_ThisCustomer.ProductFilterID.ToString();
                AddRunTimeParam("ProductFilterID", m_ThisCustomer.ProductFilterID.ToString());
            }
            else
            {
                m_SystemData.SelectSingleNode("//CustomerID").InnerText = string.Empty;
                AddRunTimeParam("CustomerID", string.Empty);

                m_SystemData.SelectSingleNode("//AffiliateID").InnerText = string.Empty;
                AddRunTimeParam("CustomerFirstName", string.Empty);

                m_SystemData.SelectSingleNode("//CustomerLastName").InnerText = string.Empty;
                AddRunTimeParam("CustomerLastName", string.Empty);

                m_SystemData.SelectSingleNode("//CustomerFullName").InnerText = string.Empty;
                AddRunTimeParam("CustomerFullName", string.Empty);

                m_SystemData.SelectSingleNode("//LocaleSetting").InnerText = m_ThisCustomer.LocaleSetting;
                AddRunTimeParam("LocaleSetting", m_ThisCustomer.LocaleSetting);

                m_SystemData.SelectSingleNode("//LanguageCode").InnerText = m_ThisCustomer.LanguageCode;
                AddRunTimeParam("LanguageCode", m_ThisCustomer.LanguageCode);

                m_SystemData.SelectSingleNode("//CurrencySetting").InnerText = PrimaryCurrency;
                AddRunTimeParam("CurrencySetting", PrimaryCurrency);

                m_SystemData.SelectSingleNode("//IPAddress").InnerText = string.Empty;
                AddRunTimeParam("IPAddress", string.Empty);

                m_SystemData.SelectSingleNode("//AffiliateID").InnerText = 0.ToString();
                AddRunTimeParam("AffiliateID", "0");
            }

            m_SystemData.SelectSingleNode("//SqlServerLocaleSetting").InnerText = Localization.GetSqlServerLocale();
            AddRunTimeParam("SqlServerLocaleSetting", Localization.GetSqlServerLocale());

            m_SystemData.SelectSingleNode("//PrimaryCurrency").InnerText = PrimaryCurrency;
            AddRunTimeParam("PrimaryCurrency", PrimaryCurrency);

            m_SystemData.SelectSingleNode("//PrimaryCurrencyDisplayLocaleFormat").InnerText = PrimaryCurrencyDisplayLocaleFormat;
            AddRunTimeParam("PrimaryCurrencyDisplayLocaleFormat", PrimaryCurrencyDisplayLocaleFormat);

            m_SystemData.SelectSingleNode("//Date").InnerText = Localization.ToNativeShortDateString(DateTime.Now);
            AddRunTimeParam("Date", Localization.ToNativeShortDateString(DateTime.Now));

            m_SystemData.SelectSingleNode("//Time").InnerText = DateTime.Now.ToShortTimeString();
            AddRunTimeParam("Time", DateTime.Now.ToShortTimeString());

            m_SystemData.SelectSingleNode("//SkinID").InnerText = SkinID.ToString();
            AddRunTimeParam("SkinID", SkinID.ToString());

            string qstr = XmlCommon.XmlEncode(HttpContext.Current.Request.QueryString.ToString());
            m_SystemData.SelectSingleNode("//QueryStringRAW").InnerText = qstr;
            AddRunTimeParam("QueryStringRAW", qstr);

            m_SystemData.SelectSingleNode("//UseStaticLinks").InnerText = AppLogic.AppConfig("UseStaticLinks");
            AddRunTimeParam("UseStaticLinks", AppLogic.AppConfigBool("UseStaticLinks").ToString());

            m_SystemData.SelectSingleNode("//XmlPackageName").InnerText = m_PackageName;
            AddRunTimeParam("XmlPackageName", m_PackageName);

            string PN = CommonLogic.GetThisPageName(false);
            m_SystemData.SelectSingleNode("//PageName").InnerText = PN;
            AddRunTimeParam("PageName", PN);

            PN = CommonLogic.GetThisPageName(true);
            m_SystemData.SelectSingleNode("//FullPageName").InnerText = PN;
            AddRunTimeParam("FullPageName", PN);

            string SURL = AppLogic.GetStoreHTTPLocation(true).ToLowerInvariant();
            m_SystemData.SelectSingleNode("//StoreUrl").InnerText = SURL;
            AddRunTimeParam("StoreUrl", SURL);

            m_SystemData.SelectSingleNode("//CurrentDateTime").InnerText = DateTime.Now.ToUniversalTime().ToString("ddd, dd MMM yyyy HH:mm:ss") + " GMT";
            AddRunTimeParam("CurrentDateTime", DateTime.Now.ToUniversalTime().ToString("ddd, dd MMM yyyy HH:mm:ss") + " GMT");

            m_SystemData.SelectSingleNode("//CustomerIsRegistered").InnerText = CommonLogic.IIF(m_ThisCustomer == null, "false", m_ThisCustomer.IsRegistered.ToString().ToLowerInvariant());
            AddRunTimeParam("CustomerIsRegistered", CommonLogic.IIF(m_ThisCustomer == null, "false", m_ThisCustomer.IsRegistered.ToString().ToLowerInvariant()));

            if (HttpContext.Current.Items.Contains("RequestedPage"))
            {
                m_SystemData.SelectSingleNode("//RequestedPage").InnerText = HttpContext.Current.Items["RequestedPage"].ToString();
            }

            if (HttpContext.Current.Items.Contains("RequestedQuerystring"))
            {
                m_SystemData.SelectSingleNode("//RequestedQuerystring").InnerText = HttpContext.Current.Items["RequestedQuerystring"].ToString();
            }

            string cia = string.Empty;
            HttpCookie myCookie = new HttpCookie("cia");
            myCookie = HttpContext.Current.Request.Cookies["cia"];

            if (myCookie != null) { cia = myCookie.Value; if (cia.Length == 0) { cia = string.Empty; } else { cia = cia.Replace("%", ","); } }
            m_SystemData.SelectSingleNode("//ItemCompareFilter").InnerText = cia;
            AddRunTimeParam("ItemCompareFilter", cia);

            m_SystemData.SelectSingleNode("//WebSiteCode").InnerText = InterpriseHelper.ConfigInstance.WebSiteCode;
            AddRunTimeParam("WebSiteCode", InterpriseHelper.ConfigInstance.WebSiteCode);

            m_SystemData.SelectSingleNode("//CustomerGuid").InnerText = ThisCustomer.ContactGUID.ToString();
            AddRunTimeParam("CustomerGuid", ThisCustomer.ContactGUID.ToString());

            m_SystemData.SelectSingleNode("//ContactGuid").InnerText = ThisCustomer.ContactGUID.ToString();
            AddRunTimeParam("ContactGuid", ThisCustomer.ContactGUID.ToString());

            m_SystemData.SelectSingleNode("//CustomerCode").InnerText = ThisCustomer.CustomerCode.ToString();
            AddRunTimeParam("CustomerCode", ThisCustomer.CustomerCode.ToString());

            m_SystemData.SelectSingleNode("//ContactCode").InnerText = ThisCustomer.CustomerCode.ToString();
            AddRunTimeParam("ContactCode", ThisCustomer.ContactCode.ToString());

            m_SystemData.SelectSingleNode("//CBNMode").InnerText = AppLogic.IsCBNMode().ToString();
            AddRunTimeParam("CBNMode", AppLogic.IsCBNMode().ToString());

            m_DataDocument.DocumentElement.AppendChild(m_DataDocument.ImportNode(m_SystemData.DocumentElement, true));

            try
            {
                //Querystring Params
                m_DataDocument.DocumentElement.AppendChild(GenerateXmlQueryStringParams());
            }
            catch { }

            //Form Params
            try
            {
                m_DataDocument.DocumentElement.AppendChild(GenerateXmlFormParams());
            }
            catch { }

            // Cookie params:
            try
            {
                m_DataDocument.DocumentElement.AppendChild(GenerateXmlCookieParams());
            }
            catch { }

            //Server Variables
            m_DataDocument.DocumentElement.AppendChild(GenerateXmlServerVariables());

            //Runtime params:
            m_DataDocument.DocumentElement.AppendChild(GenerateXmlRuntimeParams());

            //Session Params - Makes session id available to XML package
            if (HttpContext.Current.Session != null)
            {
                AddRunTimeParam("AspNetSessionId", HttpContext.Current.Session.SessionID);
            }
        }

        #region Xmlparams Generation

        private XmlNode GenerateXmlQueryStringParams()
        {
            var queryXmlRootNode = m_DataDocument.CreateNode(XmlNodeType.Element, "QueryString", string.Empty);
            var requestQuerStrings = HttpContext.Current.Request.QueryString;
            for (int i = 0; i <= requestQuerStrings.Count - 1; i++)
            {
                try
                {
                    string ParamName = requestQuerStrings.Keys[i].ToLowerInvariant();
                    string ParamValue = requestQuerStrings[requestQuerStrings.Keys[i]].ToString();
                    if (ParamName.Length != 0)
                    {
                        var queryNode = m_DataDocument.CreateNode(XmlNodeType.Element, ParamName, string.Empty);
                        queryNode.InnerText = ParamValue;
                        queryXmlRootNode.AppendChild(queryNode);
                    }
                }
                catch (Exception) { throw; }
            }
            return queryXmlRootNode;
        }

        private XmlNode GenerateXmlServerVariables()
        {
            // ServerVariables params (just the useful ones):
            var serverVarRootNode = m_DataDocument.CreateNode(XmlNodeType.Element, "ServerVariables", "");
            foreach (string s in ro_ServerVariablesList)
            {
                var serverVarNode = m_DataDocument.CreateNode(XmlNodeType.Element, s, string.Empty);
                serverVarNode.InnerText = CommonLogic.ServerVariables(s);
                serverVarRootNode.AppendChild(serverVarNode);
            }

            return serverVarRootNode;
        }

        private XmlNode GenerateXmlRuntimeParams()
        {
            var en = m_RuntimeParameters.GetEnumerator();
            var runTimeNode = m_DataDocument.CreateNode(XmlNodeType.Element, "Runtime", string.Empty);
            while (en.MoveNext())
            {
                string ParamName = en.Key.ToString();
                string ParamValue = en.Value.ToString();

                //added to handle entities with invalid characters such as spaces, & and etc.
                if (ParamName.Contains(" "))
                {
                    ParamName = ParamName.Replace(" ", "");
                }

                XmlNode node = null;
                if (ParamName.Length != 0)
                {
                    node = m_DataDocument.CreateNode(XmlNodeType.Element, ParamName, "");
                    node.InnerText = ParamValue;
                    runTimeNode.AppendChild(node);
                }
            }
            return runTimeNode;
        }

        private XmlNode GenerateXmlCookieParams()
        {
            var cookieRootNode = m_DataDocument.CreateNode(XmlNodeType.Element, "Cookies", string.Empty);
            var requestCookies = HttpContext.Current.Request.Cookies;

            if (requestCookies == null) return cookieRootNode;

            for (int i = 0; i <= requestCookies.Count - 1; i++)
            {
                string ParamName = requestCookies.Keys[i];
                string ParamValue = CommonLogic.CookieCanBeDangerousContent(ParamName, true);

                XmlNode xmlCookieNode = null;
                if (ParamName.Length != 0 && ParamName.Equals("asp.net_sessionid", StringComparison.InvariantCultureIgnoreCase) == false && ParamName.IndexOf("aspxauth", StringComparison.InvariantCultureIgnoreCase) == -1)
                {
                    try
                    {
                        if (m_DataDocument.SelectSingleNode("/root/Cookies/" + ParamName) == null)
                        {
                            xmlCookieNode = m_DataDocument.CreateNode(XmlNodeType.Element, ParamName, string.Empty);
                            xmlCookieNode.InnerText = ParamValue;
                            cookieRootNode.AppendChild(xmlCookieNode);
                        }
                    }
                    catch { throw; }
                }
            }

            return cookieRootNode;
        }

        private XmlNode GenerateXmlFormParams()
        {
            var xmlFormRootNode = m_DataDocument.CreateNode(XmlNodeType.Element, "Form", string.Empty);
            var requestForm = HttpContext.Current.Request.Form;

            for (int i = 0; i <= requestForm.Count - 1; i++)
            {
                string ParamName = requestForm.Keys[i].ToLowerInvariant();

                // add a check if the ParamName contains 'Dollar' sign, if there is a dollar sign replace it with a "_"
                XmlNode formNode = null;
                if (ParamName.Contains("$"))
                {
                    ParamName = ParamName.Replace("$", "_");
                }

                if (ParamName.Length != 0)
                {
                    formNode = m_DataDocument.CreateNode(XmlNodeType.Element, ParamName, string.Empty);
                    formNode.InnerText = requestForm[requestForm.Keys[i]].ToString();
                    xmlFormRootNode.AppendChild(formNode);
                }
            }

            return xmlFormRootNode;
        }

        #endregion

        private void GetSqlData(string OnlyRunNamedQuery)
        {
            var cn = new SqlConnection(DB.GetDBConn());
            cn.Open();

            string sql = string.Empty;
            var xml = new StringBuilder("");
            var tmpXml = new StringBuilder("");
            int numrows = 0;
            bool firstquery = true;
            string queryname = string.Empty;
            string replaceTag = string.Empty;

            string replaceType = string.Empty;
            string replaceParamName = string.Empty;
            string defaultValue = string.Empty;
            string validationpattern = string.Empty;
            string replaceValue = string.Empty;
            string RowElementName = string.Empty;            
            XmlNodeList qryNodes;
            var cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr;

            if (m_PackageDocument != null)
            {
                if (OnlyRunNamedQuery.Length > 0)
                {
                    qryNodes = m_PackageDocument.SelectNodes("/package/query[@name='" + OnlyRunNamedQuery + "']");
                }
                else
                {
                    qryNodes = DoQueryFilteringWhenCalledInEntityMgr();
                }

                foreach (XmlNode n in qryNodes)
                {
                    //determine if the query is going to be run
                    XmlNode runif = n.Attributes["runif"];
                    if (runif != null && runif.InnerText.Trim() != "")
                    {
                        string runifparam = runif.InnerText;
                        if (CommonLogic.IsStringNullOrEmpty(CommonLogic.ParamsCanBeDangerousContent(runifparam)) && 
                            CommonLogic.IsStringNullOrEmpty(AppLogic.AppConfig(runifparam)) && 
                            !m_RuntimeParameters.Contains(runifparam))
                        {
                            continue;
                        }
                    }

                    queryname = n.Attributes["name"].InnerText;
                    if (n.Attributes["rowElementName"] == null || n.Attributes["rowElementName"].InnerText.Trim() == "")
                    {
                        RowElementName = queryname + "row";
                    }
                    else
                    {
                        RowElementName = n.Attributes["rowElementName"].InnerText;
                    }


                    cmd.Parameters.Clear();
                    cmd.CommandText = "";

                    //Get the sql statement from query node (first node)

                    sql = n.FirstChild.InnerText.ToString();

                    var replacenodes = n.SelectNodes("querystringreplace");
                    foreach (XmlNode rn in replacenodes)
                    {
                        replaceTag = rn.Attributes["replaceTag"].InnerText;
                        replaceType = rn.Attributes["replacetype"].InnerText.ToLowerInvariant();
                        replaceParamName = rn.Attributes["replaceparamname"].InnerText;
                        defaultValue = rn.Attributes["defvalue"].InnerText;
                        validationpattern = rn.Attributes["validationpattern"].InnerText.Trim();

                        switch (replaceType)
                        {
                            case "request":
                                {
                                    if (CommonLogic.ParamsCanBeDangerousContent(replaceParamName) != "")
                                    {

                                        replaceValue = CommonLogic.ParamsCanBeDangerousContent(replaceParamName);
                                    }
                                    else
                                    {
                                        replaceValue = defaultValue;
                                    }

                                    break;
                                }

                            case "appconfig":
                                {
                                    replaceValue = AppLogic.AppConfig(replaceParamName);
                                    break;
                                }
                            case "webconfig":
                                {
                                    replaceValue = CommonLogic.Application(replaceParamName);
                                    break;
                                }
                            case "runtime":
                                {
                                    replaceValue = GetRuntimeParamValue(replaceParamName);
                                    break;
                                }
                            case "system":
                                {
                                    replaceValue = m_SystemData.SelectSingleNode("/System/"+ replaceParamName).InnerText;
                                    break;
                                }
                        }
                        if (validationpattern.Length > 0)
                        {
                            if (Regex.IsMatch(replaceValue, validationpattern, RegexOptions.Compiled | RegexOptions.IgnoreCase))
                            {
                                sql = sql.Replace(replaceTag, replaceValue);
                            }
                            else
                            {
                                throw new Exception("string Replace parameter " + replaceTag + " failed validation");
                            }
                        }
                        else
                        {
                            sql = sql.Replace(replaceTag, replaceValue);
                        }
                    }

                    cmd.CommandText = sql;

                    //get the query params collections

                    XmlNodeList qp = n.SelectNodes("queryparam");
                    foreach (XmlNode pn in qp)
                    {
                        //create a parameter of the appropriate type
                        cmd.Parameters.Add(CreateParameter(pn));
                    }

                    string m_DebugParamsDeclare = string.Empty;
                    string m_DebugParamsValues = string.Empty;
                    if (m_IsDebug)
                    {
                        m_SqlDebug += "************************************  SQL Statement and parameters for query " + n.Attributes["name"].InnerText + "  ************************************" + Environment.NewLine + Environment.NewLine;
                        foreach (SqlParameter sp in cmd.Parameters)
                        {
                            m_DebugParamsDeclare += "declare " + sp.ParameterName + " " + sp.SqlDbType.ToString() + Environment.NewLine;
                            string paramdatatype = sp.SqlDbType.ToString().ToLowerInvariant();
                            if (paramdatatype == "varchar" || paramdatatype == "char" || paramdatatype == "datetime" || paramdatatype == "smalldatetime" || paramdatatype == "nchar" || paramdatatype == "nvarchar" || paramdatatype == "text" || paramdatatype == "ntext" || paramdatatype == "uniqueidentifier")
                                m_DebugParamsValues += "set " + sp.ParameterName + " = '" + sp.Value.ToString() + "'" + Environment.NewLine;
                            else
                                m_DebugParamsValues += "set " + sp.ParameterName + " = " + sp.Value.ToString() + Environment.NewLine;
                        }
                        m_SqlDebug += m_DebugParamsDeclare + Environment.NewLine + m_DebugParamsValues + Environment.NewLine + Environment.NewLine;
                        m_SqlDebug += cmd.CommandText.Trim() + Environment.NewLine + Environment.NewLine;
                    }

                    dr = cmd.ExecuteReader();

                    if (n.Attributes["retType"] != null && n.Attributes["retType"].InnerText.Equals("xml", StringComparison.InvariantCultureIgnoreCase))
                    {
                        tmpXml.Append("<");
                        tmpXml.Append(queryname);
                        tmpXml.Append(">");
                        while (dr.Read())
                        {
                            tmpXml.Append(dr.GetString(0));
                        }
                        tmpXml.Append("</");
                        tmpXml.Append(queryname);
                        tmpXml.Append(">");

                        int resultset = 1;
                        while (dr.NextResult())
                        {
                            resultset++;
                            tmpXml.Append("<");
                            tmpXml.Append(queryname);
                            tmpXml.Append(resultset.ToString());
                            tmpXml.Append(">");
                            while (dr.Read())
                            {
                                tmpXml.Append(dr.GetString(0));
                            }
                            tmpXml.Append("</");
                            tmpXml.Append(queryname);
                            tmpXml.Append(resultset.ToString());
                            tmpXml.Append(">");
                        }
                    }
                    else
                    {
                        numrows = DB.GetXml(dr, queryname, RowElementName, (sql.IndexOf("aspdnsf_PageQuery") > 0), ref tmpXml);
                    }
                    if (!dr.IsClosed)
                    {
                        dr.Close();
                    }
                    dr.Dispose();

                    if (firstquery)
                    {
                        m_NumRows = numrows;
                        firstquery = false;
                    }

                    //transform the query using the specified querytransform
                    XmlNode qt = n.SelectSingleNode("querytransform");
                    if (qt != null)
                    {
                        XslCompiledTransform xsl = new XslCompiledTransform();
                        StringWriter sw = new StringWriter();
                        XmlDocument x = new XmlDocument();
                        x.LoadXml(qt.InnerXml);
                        xsl.Load(x, null, null);
                        XmlDocument xd = new XmlDocument();
                        xd.LoadXml("<root>" + m_SystemData.OuterXml + tmpXml.ToString() + "</root>");
                        xsl.Transform(xd, null, sw);
                        xml.Append(sw.ToString());
                    }
                    else
                    {
                        xml.Append(tmpXml.ToString());
                    }
                    tmpXml.Length = 0;
                }

                // execute runtime query
                IDictionaryEnumerator en = m_RuntimeQueries.GetEnumerator();
                while (en.MoveNext())
                {
                    cmd.Parameters.Clear();
                    string sqlQuery = en.Value.ToString();
                    cmd.CommandText = sqlQuery;
                    dr = cmd.ExecuteReader();
                    numrows = DB.GetXml(dr, readonly_DefaultUserQueryName, readonly_DefaultUserQueryName + "row", (sqlQuery.IndexOf("aspdnsf_PageQuery") > 0), ref xml);
                }

                cn.Close();
                cmd.Dispose();
                cn.Dispose();

                //return xml.ToString();
                if (xml.Length > 0)
                {
                    using (StringReader sr = new StringReader("<root>" + xml.ToString() + "</root>"))
                    {
                        using (XmlReader xr = XmlReader.Create(sr))
                        {
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(xr);
                            foreach (XmlNode x in xdoc.DocumentElement.ChildNodes)
                            {
                                m_DataDocument.DocumentElement.AppendChild(m_DataDocument.ImportNode(x, true));
                            }
                        }
                    }
                }
            }
            else
            {
                cn.Close();
                cmd.Dispose();
                cn.Dispose();
            }
        }

        private XmlNodeList DoQueryFilteringWhenCalledInEntityMgr()
        {
            XmlNodeList qryNodes = null;
            if (IsCalledFromEntityMgr())
            {
                string entityName = m_AdditionalRuntimeParms[0].Name;
                string entityValue = m_AdditionalRuntimeParms[0].Value;
                string filterQueryName = string.Empty;

                switch (entityValue.ToUpper())
                {
                    case DomainConstants.LOOKUP_HELPER_CATEGORIES:
                        filterQueryName = "CategoryDescriptions";
                        break;
                    case DomainConstants.LOOKUP_HELPER_DEPARTMENT:
                        filterQueryName = "DepartmentDescriptions";
                        break;
                    case DomainConstants.LOOKUP_HELPER_MANUFACTURERS:
                        filterQueryName = "ManufacturerDescriptions";
                        break;
                    case DomainConstants.LOOKUP_HELPER_ATTRIBUTE:
                        filterQueryName = "AttributeDescriptions";
                        break;
                }

                qryNodes = m_PackageDocument.SelectNodes("/package/query[@name='" + filterQueryName + "' or @name= 'EntityMgr']");
            }
            else
            {
                qryNodes = m_PackageDocument.SelectNodes("/package/query");
            }
            return qryNodes;
        }

        private bool IsCalledFromEntityMgr()
        {
            return m_AdditionalRuntimeParms != null &&
                    m_AdditionalRuntimeParms.Count == 1 &&
                    m_AdditionalRuntimeParms[0].Name.Contains("EntityName");
        }

        private SqlParameter CreateParameter(XmlNode pn)
        {
            string paramVal = string.Empty;            
            SqlParameter p = new SqlParameter();
            p.ParameterName = pn.Attributes["paramname"].InnerText;

            string requestparamname = pn.Attributes["requestparamname"].InnerText;
            string sqlDataTypeName = pn.Attributes["sqlDataType"].InnerText.ToLowerInvariant();
            string validationpattern = pn.Attributes["validationpattern"].InnerText.Trim();
            string defvalue = pn.Attributes["defvalue"].InnerText;

            //get the parameter value
            switch (pn.Attributes["paramtype"].InnerText.ToLowerInvariant())
            {
                case "request":
                    paramVal = CommonLogic.QueryStringCanBeDangerousContent(requestparamname);
                    if (paramVal == "")
                    {
                        paramVal = CommonLogic.FormCanBeDangerousContent(requestparamname);
                    }
                    else
                    {
                        break;
                    }

                    if (paramVal == "")
                    {
                        paramVal = CommonLogic.CookieCanBeDangerousContent(requestparamname, true);
                    }
                    else
                    {
                        break;
                    }

                    if (paramVal == "")
                    {
                        paramVal = CommonLogic.ServerVariables(requestparamname);
                    }
                    else
                    {
                        break;
                    }
                    break;
                case "webconfig":
                    paramVal = CommonLogic.Application(requestparamname);
                    break;
                case "appconfig":
                    paramVal = AppLogic.AppConfig(requestparamname);
                    break;
                case "runtime":
                    paramVal = GetRuntimeParamValue(requestparamname);
                    break;
                case "system":
                    paramVal = m_SystemData.SelectSingleNode("/System/" + requestparamname).InnerText;
                    break;
                case "xpath":
                    paramVal = "";
                    XmlNode x = m_DataDocument.SelectSingleNode(requestparamname);
                    if (paramVal != null)
                    {
                        paramVal = x.InnerText;
                    }
                    else
                    {
                        throw new Exception("Error executing xpath statement (" + requestparamname + ") in query param");
                    }
                    break;
            }
            if (paramVal.Trim().Length == 0)
            {
                paramVal = defvalue;
            }

            try
            {
                if (validationpattern.Length > 0)
                {
                    if (!Regex.IsMatch(paramVal, validationpattern, RegexOptions.Compiled | RegexOptions.IgnoreCase))

                    {
                        throw new Exception("Query parameter failed validation: paramvalue=" + paramVal + "; validationpattern=" + validationpattern);
                    }
                }

                switch (sqlDataTypeName)
                {
                    case "bigint":
                        p.SqlDbType = SqlDbType.BigInt;
                        p.Value = Convert.ToInt64(paramVal);
                        break;

                    case "bit":
                        p.SqlDbType = SqlDbType.Bit;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToBoolean(paramVal);
                        break;

                    case "char":
                        p.SqlDbType = SqlDbType.Char;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;

                    case "datetime":
                        p.SqlDbType = SqlDbType.DateTime;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDateTime(paramVal);
                        break;

                    case "decimal":
                        p.SqlDbType = SqlDbType.Decimal;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDecimal(paramVal);
                        break;

                    case "float":
                        p.SqlDbType = SqlDbType.Float;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDouble(paramVal);
                        break;

                    case "int":
                        p.SqlDbType = SqlDbType.Int;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToInt32(paramVal);
                        break;

                    case "money":
                        p.SqlDbType = SqlDbType.Money;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDecimal(paramVal);
                        break;

                    case "nchar":
                        p.SqlDbType = SqlDbType.NChar;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;

                    case "ntext":
                        p.SqlDbType = SqlDbType.NText;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;

                    case "nvarchar":
                        p.SqlDbType = SqlDbType.NVarChar;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;

                    case "real":
                        p.SqlDbType = SqlDbType.Real;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToSingle(paramVal);
                        break;

                    case "smalldatetime":
                        p.SqlDbType = SqlDbType.SmallDateTime;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDateTime(paramVal);
                        break;

                    case "smallint":
                        p.SqlDbType = SqlDbType.SmallInt;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToInt16(paramVal);
                        break;

                    case "smallmoney":
                        p.SqlDbType = SqlDbType.SmallMoney;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToDecimal(paramVal);
                        break;

                    case "text":
                        p.SqlDbType = SqlDbType.Text;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;

                    case "tinyint":
                        p.SqlDbType = SqlDbType.TinyInt;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = Convert.ToByte(paramVal);
                        break;

                    case "uniqueidentifier":
                        p.SqlDbType = SqlDbType.UniqueIdentifier;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = new Guid(paramVal);
                        break;

                    case "varchar":
                        p.SqlDbType = SqlDbType.VarChar;
                        if (paramVal.Equals("null", StringComparison.InvariantCultureIgnoreCase)) p.Value = DBNull.Value;
                        else p.Value = paramVal;
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Invalid parameter specification (" + ex.Message + ")");
            }
            return p;
        }             

        private void GetWebData()
        {
            StringBuilder xml = new StringBuilder("");
            StringBuilder tmpXml = new StringBuilder("");
            string url = string.Empty;
            string queryname = string.Empty;
            string rettype = string.Empty;
            string replaceTag = string.Empty;
            string replaceType = string.Empty;
            string replaceParamName = string.Empty;
            string defaultValue = string.Empty;
            string validationpattern = string.Empty;
            string replaceValue = string.Empty;            
            XmlNodeList qryNodes;


            if (m_PackageDocument != null)
            {
                qryNodes = m_PackageDocument.SelectNodes("//webquery");

                foreach (XmlNode n in qryNodes)
                {
                    XmlNode runif = n.Attributes["runif"];
                    if (runif != null && runif.InnerText.Trim() != "")
                    {
                        string runifparam = runif.InnerText;
                        if (CommonLogic.IsStringNullOrEmpty(CommonLogic.ParamsCanBeDangerousContent(runifparam)) && 
                            CommonLogic.IsStringNullOrEmpty(AppLogic.AppConfig(runifparam)))
                        {
                            continue;
                        }
                    }

                    XmlNode method = n.Attributes["method"];
                    string sendmethod = "get";
                    if (method != null && method.InnerText.Trim() != "")
                    {
                        sendmethod = method.InnerText;
                    }

                    queryname = n.Attributes["name"].InnerText;
                    rettype = n.Attributes["RetType"].InnerText;
                    url = n.SelectSingleNode("url").InnerText;

                    int timeout = 30;
                    if (n.Attributes["timeout"] != null && CommonLogic.IsInteger(n.Attributes["timeout"].InnerText))
                    {
                        timeout = Convert.ToInt32(n.Attributes["timeout"].InnerText);
                    }

                    if (url.Length == 0)
                    {
                        continue;
                    }

                    XmlNodeList replacenodes = n.SelectNodes("querystringreplace");
                    foreach (XmlNode rn in replacenodes)
                    {
                        replaceTag = rn.Attributes["replaceTag"].InnerText;
                        replaceType = rn.Attributes["replacetype"].InnerText.ToLowerInvariant();
                        replaceParamName = rn.Attributes["replaceparamname"].InnerText;
                        defaultValue = rn.Attributes["defvalue"].InnerText;
                        validationpattern = rn.Attributes["validationpattern"].InnerText.Trim();

                        switch (replaceType)
                        {
                            case "request":
                                if (CommonLogic.ParamsCanBeDangerousContent(replaceParamName) != "")
                                {
                                    replaceValue = CommonLogic.ParamsCanBeDangerousContent(replaceParamName);
                                }
                                else
                                {
                                    replaceValue = defaultValue;
                                }
                                break;
                            case "appconfig":
                                replaceValue = AppLogic.AppConfig(replaceParamName);
                                break;
                            case "webconfig":
                                replaceValue = CommonLogic.Application(replaceParamName);
                                break;
                            case "runtime":
                                replaceValue = GetRuntimeParamValue(replaceParamName);
                                break;
                            case "system":
                                replaceValue = m_SystemT.Rows[0][replaceParamName].ToString();
                                break;
                        }
                        if (validationpattern.Length > 0)
                        {
                            if (Regex.IsMatch(replaceValue, validationpattern, RegexOptions.Compiled | RegexOptions.IgnoreCase))
                            {
                                url = url.Replace(replaceTag, replaceValue);
                            }
                            else
                            {
                                throw new Exception("string Replace parameter " + replaceTag + " failed validation");
                            }
                        }
                        else
                        {
                            url = url.Replace(replaceTag, replaceValue);
                        }
                    }

                    if (sendmethod == "post")
                    {
                        XmlNode postdataNode = n.SelectSingleNode("postdata");
                        string postdata = "";
                        if (postdataNode != null)
                        {
                            string paramtype = postdataNode.Attributes["paramtype"].InnerText;
                            string paramname = postdataNode.Attributes["paramname"].InnerText.ToLowerInvariant();
                            switch (paramtype)
                            {
                                case "request":
                                    postdata = CommonLogic.ParamsCanBeDangerousContent(paramname);
                                    break;
                                case "appconfig":
                                    postdata = AppLogic.AppConfig(paramname);
                                    break;
                                case "webconfig":
                                    postdata = CommonLogic.Application(paramname);
                                    break;
                                case "runtime":
                                    postdata = GetRuntimeParamValue(paramname);
                                    break;
                                case "system":
                                    postdata = m_SystemT.Rows[0][paramname].ToString();
                                    break;
                            }
                        }
                        tmpXml.Append(CommonLogic.AspHTTP(url, timeout, postdata));
                    }
                    else
                    {
                        tmpXml.Append(CommonLogic.AspHTTP(url, timeout));
                    }

                    //transform the results using the specified querytransform
                    if (rettype == "xml")
                    {
                        XmlDocument xdoc = new XmlDocument();
                        try
                        {
                            xdoc.LoadXml(tmpXml.ToString());
                            XmlNode qt = n.SelectSingleNode("querytransform");
                            if (qt != null)
                            {
                                XslCompiledTransform xsl = new XslCompiledTransform();
                                StringWriter sw = new StringWriter();
                                XmlDocument x = new XmlDocument();
                                x.LoadXml(qt.InnerXml);
                                xsl.Load(x, null, null);
                                XmlDocument xd = new XmlDocument();
                                xd.LoadXml("<root>" + m_SystemData.OuterXml + xdoc.DocumentElement.OuterXml + "</root>");
                                xsl.Transform(xd, null, sw);
                                xml.Append(sw.ToString());
                            }
                            else
                            {
                                xml.Append("<" + queryname + ">" + xdoc.DocumentElement.OuterXml + "</" + queryname + ">");
                            }
                        }
                        catch
                        {
                            xml.Append("<" + queryname + ">" + "<![CDATA[" + tmpXml.ToString() + "]]>" + "</" + queryname + ">");
                        }
                    }
                    else
                    {
                        xml.Append("<" + queryname + ">" + "<![CDATA[" + tmpXml.ToString() + "]]>" + "</" + queryname + ">");
                    }
                    tmpXml.Length = 0;
                }

                if (xml.Length > 0)
                {
                    using (StringReader sr = new StringReader(xml.ToString()))
                    {
                        using (XmlReader xr = XmlReader.Create(sr))
                        {
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(xr);
                            m_DataDocument.DocumentElement.AppendChild(m_DataDocument.ImportNode(xdoc.DocumentElement, true));
                        }
                    }
                }

            }
        }

        private void AddRunTimeQuery(string QueryName, string UserSpecifiedQuery)
        {
            if (m_RuntimeQueries.Contains(QueryName))
            {
                m_RuntimeQueries.Remove(QueryName);
            }
            m_RuntimeQueries.Add(QueryName, UserSpecifiedQuery);
        }

        private void AddRunTimeParam(string ParameterName, string ParameterValue)
        {
            if (m_RuntimeParameters.Contains(ParameterName))
            {
                m_RuntimeParameters.Remove(ParameterName);
            }
            m_RuntimeParameters.Add(ParameterName, ParameterValue);
        }

        private void AddPackageNode(XmlNode xNode)
        {
            string packageName = xNode.Attributes["name"].InnerText;
            using (StringReader sr = new StringReader(AppLogic.RunXmlPackage(packageName, null, ThisCustomer, SkinID, string.Empty, null, false, false)))
            {
                using (XmlReader xr = XmlReader.Create(sr))
                {
                    XmlDocument xNew = new XmlDocument();
                    xNew.Load(xr);
                    XmlNode xNewChild = xNode.OwnerDocument.ImportNode(xNew.DocumentElement, true);
                    xNode.ParentNode.ReplaceChild(xNewChild, xNode);
                }
            }
        }

        private string GetRuntimeParamValue(string FindParamName)
        {
            IDictionaryEnumerator en = m_RuntimeParameters.GetEnumerator();
            while (en.MoveNext())
            {
                string ParamName = en.Key.ToString();
                string ParamValue = en.Value.ToString();

                if (ParamName.Equals(FindParamName, StringComparison.InvariantCultureIgnoreCase))
                {
                    return ParamValue;
                }
            }
            return string.Empty;
        }

        private string AddParameterNode(XmlNode pNode, string sqlQuery)
        {
            if (pNode.Attributes["name"] != null)
            {
                string paramName = pNode.Attributes["name"].InnerText;
                string paramValue = string.Empty;
                if (pNode.Attributes["ise:appconfig"] != null)
                {
                    paramValue = AppLogic.AppConfig(pNode.Attributes["ise:appconfig"].InnerText);
                }
                if (pNode.Attributes["ise:params"] != null)
                {
                    paramValue = CommonLogic.ParamsCanBeDangerousContent(pNode.Attributes["ise:params"].InnerText);
                }
                if (pNode.Attributes["ise:runtime"] != null)
                {
                    paramValue = GetRuntimeParamValue(paramName); 
                }
                // try getting a default from the Xsl File (e.g. <xsl:param ...>defaultvalue</xsl:param>
                if (paramValue.Length == 0)
                {
                    paramValue = pNode.InnerText;
                }
                pNode.InnerText = paramValue;
                if (paramName.Length != 0)
                {
                    sqlQuery = sqlQuery.Replace("{" + paramName + "}", paramValue); //Get the value from Params
                }
            }
            return sqlQuery;
        }
        private void ProcessSESettings()
        {
            XmlNode SESettings = m_PackageDocument.SelectSingleNode("/package/SearchEngineSettings");
            if (SESettings != null)
            {
                XslCompiledTransform t;
                XmlDocument x = new XmlDocument();
                StringWriter xsw;
                XmlNode XPathResult;

                XmlNode SectionTitle = SESettings.SelectSingleNode("SectionTitle");
                XmlNode SETitle = SESettings.SelectSingleNode("SETitle");
                XmlNode SEKeywords = SESettings.SelectSingleNode("SEKeywords");
                XmlNode SEDescription = SESettings.SelectSingleNode("SEDescription");
                XmlNode SENoScript = SESettings.SelectSingleNode("SENoScript");

                if (SectionTitle != null)
                {
                    if (SectionTitle.Attributes["actionType"] == null)
                    {
                        throw new Exception("actionType attribute not specified for SectionTitle element");
                    }
                    switch (SectionTitle.Attributes["actionType"].InnerText)
                    {
                        case "xpath":
                            string xpath = SectionTitle.InnerText;
                            XPathResult = m_DataDocument.SelectSingleNode(xpath);
                            if (XPathResult != null)
                            {
                                m_SectionTitle = XPathResult.InnerText;
                            }
                            break;

                        case "transform":
                            xsw = new StringWriter();
                            t = new XslCompiledTransform();
                            x.LoadXml(SectionTitle.InnerXml);
                            t.Load(x);
                            t.Transform(m_DataDocument, m_TransformArgumentList, xsw);
                            m_SectionTitle = xsw.ToString();
                            break;

                        case "text":
                            m_SectionTitle = SectionTitle.InnerText.Replace("\r\n", "").Trim();
                            break;
                    }


                }

                if (SETitle != null)
                {
                    if (SETitle.Attributes["actionType"] == null)
                    {
                        throw new Exception("actionType attribute not specified for SETitle element");
                    }
                    switch (SETitle.Attributes["actionType"].InnerText)
                    {
                        case "xpath":
                            string xpath = SETitle.InnerText;
                            XPathResult = m_DataDocument.SelectSingleNode(xpath);
                            if (XPathResult != null)
                            {
                                m_SETitle = XPathResult.InnerText;
                            }
                            break;

                        case "transform":
                            xsw = new StringWriter();
                            t = new XslCompiledTransform();
                            x.LoadXml(SETitle.InnerXml);
                            t.Load(x);
                            t.Transform(m_DataDocument, m_TransformArgumentList, xsw);
                            m_SETitle = xsw.ToString();
                            break;

                        case "text":
                            m_SETitle = SETitle.InnerText.Replace("\r\n", "").Trim();
                            break;
                    }
                }

                if (SEKeywords != null)
                {
                    if (SEKeywords.Attributes["actionType"] == null)
                    {
                        throw new Exception("actionType attribute not specified for SEKeywords element");
                    }
                    switch (SEKeywords.Attributes["actionType"].InnerText)
                    {
                        case "xpath":
                            string xpath = SEKeywords.InnerText;
                            XPathResult = m_DataDocument.SelectSingleNode(xpath);
                            if (XPathResult != null)
                            {
                                m_SEKeywords = XPathResult.InnerText;
                            }
                            break;

                        case "transform":
                            xsw = new StringWriter();
                            t = new XslCompiledTransform();
                            x.LoadXml(SEKeywords.InnerXml);
                            t.Load(x);
                            t.Transform(m_DataDocument, m_TransformArgumentList, xsw);
                            m_SEKeywords = xsw.ToString();
                            break;

                        case "text":
                            m_SEKeywords = SEKeywords.InnerText.Replace("\r\n", "").Trim();
                            break;
                    }

                }

                if (SEDescription != null)
                {
                    if (SEDescription.Attributes["actionType"] == null)
                    {
                        throw new Exception("actionType attribute not specified for SEDescription element");
                    }
                    switch (SEDescription.Attributes["actionType"].InnerText)
                    {
                        case "xpath":
                            string xpath = SEDescription.InnerText;
                            XPathResult = m_DataDocument.SelectSingleNode(xpath);
                            if (XPathResult != null)
                            {
                                m_SEDescription = XPathResult.InnerText;
                            }
                            break;

                        case "transform":
                            xsw = new StringWriter();
                            t = new XslCompiledTransform();
                            x.LoadXml(SEDescription.InnerXml);
                            t.Load(x);
                            t.Transform(m_DataDocument, m_TransformArgumentList, xsw);
                            m_SEDescription = xsw.ToString();
                            break;
                        case "text":
                            m_SEDescription = SEDescription.InnerText.Replace("\r\n", "").Trim();
                            break;
                    }
                }

                if (SENoScript != null)
                {
                    if (SENoScript.Attributes["actionType"] == null)
                    {
                        throw new Exception("actionType attribute not specified for SENoScript element");
                    }
                    switch (SENoScript.Attributes["actionType"].InnerText)
                    {
                        case "xpath":
                            string xpath = SENoScript.InnerText;
                            XPathResult = m_DataDocument.SelectSingleNode(xpath);
                            if (XPathResult != null)
                            {
                                m_SENoScript = XPathResult.InnerText;
                            }
                            break;

                        case "transform":
                            xsw = new StringWriter();
                            t = new XslCompiledTransform();
                            x.LoadXml(SENoScript.InnerXml);
                            t.Load(x);
                            t.Transform(m_DataDocument, m_TransformArgumentList, xsw);
                            m_SENoScript = xsw.ToString();
                            break;

                        case "text":
                            m_SENoScript = SENoScript.InnerText.Replace("\r\n", "").Trim();
                            break;
                    }

                }

            }
        }
        private void ProcessAfterActions()
        {
            XmlNodeList q;
            XmlNode PostProcessing = m_PackageDocument.SelectSingleNode("/package/PostProcessing");
            if (PostProcessing != null)
            {
                q = PostProcessing.SelectNodes("/package/PostProcessing/queryafter");
                foreach (XmlNode n in q)
                {
                    ProcessSQLAfterActions(n);
                }

                q = PostProcessing.SelectNodes("/package/PostProcessing/webqueryafter");
                foreach (XmlNode n in q)
                {
                    ProcessWebQueryAfterActions(n);
                }

                q = PostProcessing.SelectNodes("/package/PostProcessing/setcookie");
                foreach (XmlNode n in q)
                {
                    ProcessCookieAfterActions(n);
                }
            }
        }
        private void ProcessSQLAfterActions(XmlNode n)
        {
            SqlConnection cn = new SqlConnection(DB.GetDBConn());
            cn.Open();

            string sql = string.Empty;
            StringBuilder xml = new StringBuilder("");
            StringBuilder tmpXml = new StringBuilder("");
            string queryname = string.Empty;
            string replaceTag = string.Empty;

            string replaceType = string.Empty;
            string replaceParamName = string.Empty;
            string defaultValue = string.Empty;
            string validationpattern = string.Empty;
            string replaceValue = string.Empty;
            string RowElementName = string.Empty;
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;

            //determine if the query is going to be run
            XmlNode runif = n.SelectSingleNode("runif");
            if (runif != null)
            {
                string runifparam = runif.Attributes["paramsource"].InnerText;
                switch (runif.Attributes["paramtype"].InnerText)
                {
                    case "request":
                    case "appconfig":
                        if (CommonLogic.IsStringNullOrEmpty(CommonLogic.ParamsCanBeDangerousContent(runifparam)) && 
                            CommonLogic.IsStringNullOrEmpty(AppLogic.AppConfig(runifparam)))
                        {
                            return;
                        }
                        break;
                    case "xpath":
                        if (m_DataDocument.SelectSingleNode(runifparam) == null)
                        {
                            return;
                        }
                        break;
                    default:
                        return;
                }
            }

            cmd.Parameters.Clear();
            cmd.CommandText = "";

            sql = n.FirstChild.InnerText.ToString();
            XmlNodeList replacenodes = n.SelectNodes("querystringreplace");
            foreach (XmlNode rn in replacenodes)
            {
                replaceTag = rn.Attributes["replaceTag"].InnerText;
                replaceType = rn.Attributes["replacetype"].InnerText.ToLowerInvariant();
                replaceParamName = rn.Attributes["replaceparamname"].InnerText;
                defaultValue = rn.Attributes["defvalue"].InnerText;
                validationpattern = rn.Attributes["validationpattern"].InnerText.Trim();

                replaceValue = "";
                switch (replaceType)
                {
                    case "request":
                        replaceValue = CommonLogic.ParamsCanBeDangerousContent(replaceParamName);
                        break;
                    case "appconfig":
                        replaceValue = AppLogic.AppConfig(replaceParamName);
                        break;
                    case "webconfig":
                        replaceValue = CommonLogic.Application(replaceParamName);
                        break;
                    case "runtime":
                        replaceValue = GetRuntimeParamValue(replaceParamName);
                        break;
                    case "xpath":
                        XmlNode x = m_DataDocument.SelectSingleNode(replaceParamName);
                        if (x != null)
                        {
                            replaceValue = x.InnerText;
                        }
                        break;
                }
                if (replaceValue.Trim().Length == 0)
                {
                    replaceValue = defaultValue;
                }
                if (validationpattern.Length > 0)
                {
                    if (Regex.IsMatch(replaceValue, validationpattern, RegexOptions.IgnoreCase | RegexOptions.Compiled))
                    {
                        sql = sql.Replace(replaceTag, replaceValue);
                    }
                    else
                    {
                        throw new Exception("string Replace parameter " + replaceTag + " failed validation");
                    }
                }
                else
                {
                    sql = sql.Replace(replaceTag, replaceValue);
                }
            }

            cmd.CommandText = sql;

            XmlNodeList qp = n.SelectNodes("queryparam");
            foreach (XmlNode pn in qp)
            {
                //create a parameter of the appropriate type
                cmd.Parameters.Add(CreateParameter(pn));
            }
            cmd.ExecuteNonQuery();

            cn.Close();
            cmd.Dispose();
            cn.Dispose();

        }
        private void ProcessWebQueryAfterActions(XmlNode n)
        {
            StringBuilder xml = new StringBuilder("");
            StringBuilder tmpXml = new StringBuilder("");
            string url = string.Empty;
            string queryname = string.Empty;
            string rettype = string.Empty;
            string replaceTag = string.Empty;
            string replaceType = string.Empty;
            string replaceParamName = string.Empty;
            string defaultValue = string.Empty;
            string validationpattern = string.Empty;
            string replaceValue = string.Empty;            

            //determine if the query is going to be run
            XmlNode runif = n.SelectSingleNode("runif");
            if (runif != null)
            {
                string runifparam = runif.Attributes["paramsource"].InnerText;
                switch (runif.Attributes["paramtype"].InnerText)
                {
                    case "request":
                    case "appconfig":
                        if (CommonLogic.IsStringNullOrEmpty(CommonLogic.ParamsCanBeDangerousContent(runifparam)) && 
                            CommonLogic.IsStringNullOrEmpty(AppLogic.AppConfig(runifparam)))
                        {
                            return;
                        }
                        break;
                    case "xpath":
                        if (m_DataDocument.SelectSingleNode(runifparam) == null)
                        {
                            return;
                        }
                        break;
                    default:
                        return;
                }
            }
            queryname = n.Attributes["name"].InnerText;
            rettype = n.Attributes["RetType"].InnerText;
            url = n.SelectSingleNode("url").InnerText;
            if (url.Length == 0)
            {
                return;
            }

            XmlNodeList replacenodes = n.SelectNodes("querystringreplace");
            foreach (XmlNode rn in replacenodes)
            {
                replaceTag = rn.Attributes["replaceTag"].InnerText;
                replaceType = rn.Attributes["replacetype"].InnerText.ToLowerInvariant();
                replaceParamName = rn.Attributes["replaceparamname"].InnerText;
                defaultValue = rn.Attributes["defvalue"].InnerText;
                validationpattern = rn.Attributes["validationpattern"].InnerText.Trim();

                switch (replaceType)
                {
                    case "request":
                        replaceValue = CommonLogic.ParamsCanBeDangerousContent(replaceParamName);
                        break;

                    case "appconfig":
                        replaceValue = AppLogic.AppConfig(replaceParamName);
                        break;
                    case "webconfig":
                        replaceValue = CommonLogic.Application(replaceParamName);
                        break;
                    case "runtime":
                        replaceValue = GetRuntimeParamValue(replaceParamName);
                        break;
                    case "xpath":
                        XmlNode x = m_DataDocument.SelectSingleNode(replaceParamName);
                        if (x != null)
                        {
                            replaceValue = x.InnerText;
                        }
                        break;
                }
                if (replaceValue.Length == 0)
                {
                    replaceValue = defaultValue;
                }

                if (validationpattern.Length > 0)
                {
                    if (Regex.IsMatch(replaceValue, validationpattern, RegexOptions.IgnoreCase | RegexOptions.Compiled))
                    {
                        url = url.Replace(replaceTag, replaceValue);
                    }
                    else
                    {
                        throw new Exception("string Replace parameter " + replaceTag + " failed validation");
                    }
                }
                else
                {
                    url = url.Replace(replaceTag, replaceValue);
                }
            }
            tmpXml.Append(CommonLogic.AspHTTP(url, 30));

        }
        private void ProcessCookieAfterActions(XmlNode n)
        {
            string valuetype = n.Attributes["valuetype"].InnerText.ToLowerInvariant();
            string cookiesource = n.Attributes["cookiesource"].InnerText;
            string cookiename = n.Attributes["cookiename"].InnerText;
            string cookievalue = string.Empty;
            double cookieexpires = 0;
            if (n.Attributes["expires"] != null)
            {
                cookieexpires = Convert.ToDouble(n.Attributes["expires"].InnerText.Trim());
            }

            switch (valuetype)
            {
                case "request":
                    cookievalue = CommonLogic.ParamsCanBeDangerousContent(cookiesource);
                    break;
                case "appconfig":
                    cookievalue = AppLogic.AppConfig(cookiesource);
                    break;
                case "webconfig":
                    cookievalue = CommonLogic.Application(cookiesource);
                    break;
                case "xpath":
                    XmlNode x = m_DataDocument.SelectSingleNode(cookiesource);
                    if (x != null)
                    {
                        cookievalue = x.InnerText;
                    }
                    break;
            }
            HttpContext.Current.Response.Cookies[cookiename].Value = cookievalue;
            if (cookieexpires > 0)
            {
                HttpContext.Current.Response.Cookies[cookiename].Expires = DateTime.Now.AddDays(cookieexpires);
            }
        }

        private string StringResourceMatch(Match match)
        {
            string l = match.Groups[1].Value;
            string s = HttpUtility.HtmlDecode(AppLogic.GetString(l, m_ThisCustomer.SkinID, m_ThisCustomer.LocaleSetting));
            if (s == null || s.Length == 0 || s == l)
            {
                s = match.Value;
            }
            return s;
        }

        #endregion
    }
}
